/*
EXEC SP_WORK_ORDER_NOT_STARTED_PER_PLAN '0', 0, 0, '0', '0', 0, '0', '01/30/2006', '0',2, '0'

SELECT * FROM ASSEMBLY_LOT_HISTORY WHERE LOT_ID = 3 
*/
ALTER PROC SP_WORK_ORDER_NOT_STARTED_PER_PLAN 
(
	@PS_TYPE_NAME 	VARCHAR(50),
	@BASE_PRODUCT_ID	INT,
	@PROCESSSTEP_ID 	INT,
	@CARD_TYPE		VARCHAR(50),
	@PACKAGE_TYPE	VARCHAR(50),
	@MEMORY_SIZE		VARCHAR(50),
	@Date_Type		varchar(20),
	@FROM_DATE		VARCHAR(20),
	@TO_DATE		VARCHAR(20),
	@IS_NOT_PER_PLAN	INT,
	@DEFAULT_PROMPT 	VARCHAR(20)

)
AS
BEGIN


	DECLARE @RESULTS_TABLE TABLE ( PRODUCT_TYPE_ID int, BASE_PRODUCT_NAME varchar(50), PS_TYPE_NAME_ID int, PS_TYPE_NAME varchar(50), PROCESSSTEP_ID BIGINT, ITEM_NUMBER VARCHAR(50), ITEM_DESC VARCHAR(500), LOT_ID INT, 
				WORK_ORDER_NO VARCHAR(50), QTY_OUT INT, QTY_REJECTED INT, QTY_IN INT, QAD_ORD_DATE DATETIME, QAD_REL_DATE DATETIME, QAD_DUE_DATE DATETIME, 
				SOD DATETIME, QAD_SOD_LATE_DAYS INT, REVISED_SOD DATETIME, LOAD_TIME DATETIME, ASSM_QTY INT, 
				[2/0] INT, DA INT, WB INT, MOLD INT, PLAT INT, FORM INT, PACK INT, WO_STATUS VARCHAR(50),[ACTUAL CYCLE TIME] INT )

	CREATE TABLE #CYCLE_TIME_RESULTS_TABLE ( PRODUCT_TYPE_ID int, BASE_PRODUCT_NAME varchar(50), PS_TYPE_NAME_ID int, PS_TYPE_NAME varchar(50), PROCESSSTEP_ID BIGINT, ITEM_NUMBER VARCHAR(50), ITEM_DESC VARCHAR(500), LOT_ID INT, 
				WORK_ORDER_NO VARCHAR(50), QTY_OUT INT, QTY_REJECTED INT, QTY_IN INT, QAD_ORD_DATE DATETIME, QAD_REL_DATE DATETIME, QAD_DUE_DATE DATETIME, 
				SOD DATETIME, QAD_SOD_LATE_DAYS INT, REVISED_SOD DATETIME, LOAD_TIME DATETIME, ASSM_QTY INT DEFAULT 0, 
				[2/0] INT DEFAULT 0, DA INT, WB INT, MOLD INT, PLAT INT, FORM INT, PACK INT, WO_STATUS VARCHAR(50),[ACTUAL CYCLE TIME] INT, MIN_STAGE_NAME VARCHAR(50), MIN_STAGE_NAME_QTY INT, CYCLE_TIME INT , SHIPPABLE_MB BIGINT )
	
	DECLARE @SQLSTRING NVARCHAR(4000)


	IF ( @FROM_DATE IS NULL OR @FROM_DATE = '') 
	BEGIN
		SET @FROM_DATE = '0'
	END
	IF ( @TO_DATE IS NULL OR @TO_DATE = '') 
	BEGIN
		SET @TO_DATE = '0'
	END


	INSERT INTO @RESULTS_TABLE 
	SELECT
		CASE 
			WHEN p_type is Null THEN 1
			WHEN p_type = 'BOARD' THEN 2
			WHEN p_type ='KIT' THEN 3
		END AS PRODUCT_TYPE_ID, 
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = CASE 
			WHEN P.P_PARENT_PRODUCT_ID = -1 THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID <> -1 THEN P.P_PARENT_PRODUCT_ID	
		 END 
		) AS BASE_PRODUCT_NAME,
		CASE 
			WHEN PS_TYPE_NAME = 'FAB' THEN 1
			WHEN PS_TYPE_NAME = 'Wafer Store' THEN 2
			WHEN PS_TYPE_NAME = 'SORT' THEN 3
			WHEN PS_TYPE_NAME = 'Die Bank' THEN 4
			WHEN PS_TYPE_NAME = 'Assembly' THEN 5
			WHEN PS_TYPE_NAME = 'TEST' THEN 6
			WHEN PS_TYPE_NAME = 'Class Store' THEN 7
			WHEN PS_TYPE_NAME = 'Mark & Pack' or PS_TYPE_NAME = 'Mark &amp; Pack' THEN 8
			WHEN PS_TYPE_NAME = 'Finished Goods' THEN 9
			ELSE 0
		END AS PS_TYPE_NAME_ID,
		PS_TYPE_NAME,
		P.PROCESSSTEP_ID,
		QT.ITEM_NUMBER, 
		QT.ITEM_DESC, 
		LT.LOT_ID,
		QT.WORK_ORDER_NO, WRLT.QTY_OUT, WRLT.QTY_REJECTED,
		( CASE
			WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT TOP 1 ALH.AL_ACTUAL_QTY  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE (WRLT.QTY_IN) END
		) as [WRLT.QTY_IN], 
		WRLT.ISSUE_DATE , WRLT.RELEASE_DATE AS QAD_REL_DATE, 
		WRLT.DATE_OUT AS QAD_DUE_DATE, 
		( CASE
			WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT TOP 1 ALH.AL_EXPECTED_DATE_OUT  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_EXPECTED_DATE_OUT  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID)  END
		) AS SOD,
		DATEDIFF(D, QT.DUE_DATE , CASE
						WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT TOP 1 ISNULL( AL_REVISED_DATE_OUT, AL_EXPECTED_DATE_OUT )  FROM ASSEMBLY_LOT_HISTORY  WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
						ELSE
						(SELECT ISNULL( AL.AL_REVISED_DATE_OUT, AL.AL_EXPECTED_DATE_OUT )  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID) END 
		) AS [QAD SOD LATE DAYS],
		( CASE
			WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT TOP 1 ALH.AL_REVISED_DATE_OUT  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_REVISED_DATE_OUT  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID)  END 
		) AS REVISE_SOD,
		(  CASE
			WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT TOP 1 ALH.AL_LOAD_DATE  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_LOAD_DATE  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID) END
		) AS LOAD_DATE,
		( CASE
			WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT TOP 1 ALH.AL_QTY_IN  FROM ASSEMBLY_LOT_HISTORY ALH WHERE LOT_ID = LT.LOT_ID ORDER BY TIMESTAMP DESC ) 
			ELSE
			(SELECT AL.AL_QTY_IN  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID ) END
		) AS ASSM_QTY,

		(SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		 WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = '2nd Optical Inspection' AND LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS ='Running'  
		 )AS [2/O],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Die Attach' AND LOT_ID = LT.LOT_ID  AND LOT_CURRENT_STATUS ='Running'
		) AS [DA],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Wire Bond' AND LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS ='Running'
		) AS [WB],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Molding' AND LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS ='Running'
		) AS [MOLD],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Plating' AND LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS ='Running'
		) AS [PLAT],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Forming' AND LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS ='Running'
		) AS [FORM],
		( SELECT ASL_QTY_IN  FROM ASSEMBLY_STAGE_LOT ASL, ASSEMBLY_STAGE_TABLE AST 
		  WHERE ASL.STAGE_ID = AST.STAGE_ID AND AST_STAGE_NAME = 'Packing' AND LOT_ID = LT.LOT_ID AND LOT_CURRENT_STATUS ='Running'
		) AS [PACK], QT.WO_STATUS,
		( DATEDIFF (D , (SELECT top 1 AHL.AL_LOAD_DATE  FROM ASSEMBLY_LOT_HISTORY AHL WHERE LOT_ID = LT.LOT_ID order by timestamp desc), 
				( CASE
					WHEN LOT_CURRENT_STATUS = 'CLOSE' THEN (SELECT top 1 ISNULL( AHL.AL_DATE_OUT, AHL.TIMESTAMP )  FROM ASSEMBLY_LOT_HISTORY AHL WHERE LOT_ID = LT.LOT_ID order by timestamp desc) 
					ELSE
					( SELECT ISNULL( AL.AL_REVISED_DATE_OUT, AL.AL_EXPECTED_DATE_OUT )  FROM ASSEMBLY_LOT AL WHERE LOT_ID = LT.LOT_ID) END 
				)		   		
			      )  
		) AS [ACTUAL CYCLE TIME]
		
	
	FROM LOT_TABLE LT, QAD_TABLE QT, PRODUCT P, PROCESS_STEP PS, WORK_ORDER_LOT_TABLE WRLT
	WHERE 
		QT.WORK_ORDER_NO = LT.LOT_NUMBER AND 
		QT.ITEM_NUMBER = P.PART_NUMBER AND 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID  AND 
		P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND 
		LT.LOT_ID = WRLT.LOT_ID	

	INSERT INTO #CYCLE_TIME_RESULTS_TABLE SELECT RT.*,  
	( CASE 	WHEN RT.[2/0] IS NOT NULL AND RT.[2/0] > 0 THEN '2/0'
		WHEN RT.[DA] IS NOT NULL AND RT.[DA] > 0 THEN 'DA'
		WHEN RT.[WB] IS NOT NULL AND RT.[WB] > 0 THEN 'WB'
		WHEN RT.[MOLD] IS NOT NULL AND RT.[MOLD] > 0 THEN 'MOLD'
		WHEN RT.[PLAT] IS NOT NULL AND RT.[PLAT] > 0 THEN 'PLAT'
		WHEN RT.[FORM] IS NOT NULL AND RT.[FORM] > 0 THEN 'FORM'
		WHEN RT.[PACK] IS NOT NULL AND RT.[PACK] > 0 THEN 'Packing' END ) AS MIN_STAGE_NAME,
 	( CASE 	WHEN RT.[2/0] IS NOT NULL AND RT.[2/0] > 0 THEN RT.[2/0]
		WHEN RT.[DA] IS NOT NULL AND RT.[DA] > 0 THEN RT.[DA]
		WHEN RT.[WB] IS NOT NULL AND RT.[WB] > 0 THEN RT.[WB]
		WHEN RT.[MOLD] IS NOT NULL AND RT.[MOLD] > 0 THEN RT.[MOLD]
		WHEN RT.[PLAT] IS NOT NULL AND RT.[PLAT] > 0 THEN  RT.[PLAT]
		WHEN RT.[FORM] IS NOT NULL AND RT.[FORM] > 0 THEN RT.[FORM]
		WHEN RT.[PACK] IS NOT NULL AND RT.[PACK] > 0 THEN RT.[PACK] END ) AS MIN_STAGE_NAME_QTY,
	( CASE 	WHEN RT.[2/0] IS NOT NULL AND RT.[2/0] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID  AND AST_STAGE_NAME IN ( '2nd Optical Inspection' ) ) 
		WHEN RT.[DA] IS NOT NULL AND RT.[DA] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID  AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach' ) )
		WHEN RT.[WB] IS NOT NULL AND RT.[WB] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID   AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond' ) )
		WHEN RT.[MOLD] IS NOT NULL AND RT.[MOLD] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID  AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding' ) )
		WHEN RT.[PLAT] IS NOT NULL AND RT.[PLAT] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID  AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding', 'Plating' ) ) 
		WHEN RT.[FORM] IS NOT NULL AND RT.[FORM] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID  AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding', 'Plating', 'Forming' ) )
		WHEN RT.[PACK] IS NOT NULL AND RT.[PACK] > 0 THEN ( SELECT ISNULL( SUM(AST_CYCLE_TIME), 0 ) FROM ASSEMBLY_STAGE_TABLE AST WHERE AST.PROCESSSTEP_ID = RT.PROCESSSTEP_ID  AND AST_STAGE_NAME IN ( '2nd Optical Inspection', 'Die Attach', 'Wire Bond', 'Molding', 'Plating', 'Forming', 'Packing' ) ) END ) AS CYCLE_TIME,
	( CASE 	WHEN RT.[QTY_IN] IS NOT NULL AND RT.[QTY_IN] > 0 THEN ( RT.[QTY_IN] * ( SELECT CASE 
		WHEN PARAMETER_VALUE = '64' THEN 64
		WHEN PARAMETER_VALUE = '128' THEN 128
		WHEN PARAMETER_VALUE = '256' THEN 256
		WHEN PARAMETER_VALUE = '512' THEN 512
		WHEN PARAMETER_VALUE LIKE '1 GB' THEN 1024
		WHEN PARAMETER_VALUE LIKE '2 GB' THEN 2048
		ELSE 2048 
	END
		FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'MEMORY SIZE' and PROCESSSTEP_ID = RT.PROCESSSTEP_ID ) ) END ) AS SHIPPABLE_MB

	FROM @RESULTS_TABLE RT

	IF ( @IS_NOT_PER_PLAN = 0 )-- Flag for Work Orders Not Started Per Plan
	BEGIN
		SET @SQLSTRING = N'SELECT PRODUCT_TYPE_ID, BASE_PRODUCT_NAME, PS_TYPE_NAME_ID, PS_TYPE_NAME, PROCESSSTEP_ID, ITEM_NUMBER, ITEM_DESC, LOT_ID, WORK_ORDER_NO, QTY_OUT, QTY_REJECTED, QTY_IN, CONVERT( VARCHAR, QAD_ORD_DATE, 101 ) AS QAD_ORD_DATE, CONVERT( VARCHAR, QAD_REL_DATE, 101 ) AS QAD_REL_DATE, CONVERT( VARCHAR, QAD_DUE_DATE, 101 ) AS QAD_DUE_DATE, CONVERT( VARCHAR, SOD, 101 ) AS SOD, QAD_SOD_LATE_DAYS, CONVERT( VARCHAR, REVISED_SOD, 101 ) AS REVISED_SOD, CONVERT( VARCHAR, LOAD_TIME, 101 )AS LOAD_TIME, ASSM_QTY, [2/0], DA, WB, MOLD, PLAT, FORM, PACK, MIN_STAGE_NAME, MIN_STAGE_NAME_QTY, CYCLE_TIME, SHIPPABLE_MB, [ACTUAL CYCLE TIME],WO_STATUS FROM #CYCLE_TIME_RESULTS_TABLE CTRT
		WHERE  DATEADD( d, ISNULL( CYCLE_TIME, 0), QAD_REL_DATE ) < CONVERT( VARCHAR, GETDATE(), 101 ) and MIN_STAGE_NAME is NOT null '
	END
	ELSE --IF ( @IS_NOT_PER_PLAN = 1 )-- Loading Runnning and Closed Work Orders if specified Data Range otherwise loading all running lots
	BEGIN
		SET @SQLSTRING = N'SELECT PRODUCT_TYPE_ID, BASE_PRODUCT_NAME, PS_TYPE_NAME_ID, PS_TYPE_NAME, PROCESSSTEP_ID, ITEM_NUMBER, ITEM_DESC, LOT_ID, WORK_ORDER_NO, QTY_OUT, QTY_REJECTED, QTY_IN, CONVERT( VARCHAR, QAD_ORD_DATE, 101 ) AS QAD_ORD_DATE, CONVERT( VARCHAR, QAD_REL_DATE, 101 ) AS QAD_REL_DATE, CONVERT( VARCHAR, QAD_DUE_DATE, 101 ) AS QAD_DUE_DATE, CONVERT( VARCHAR, SOD, 101 ) AS SOD, QAD_SOD_LATE_DAYS, CONVERT( VARCHAR, REVISED_SOD, 101 ) AS REVISED_SOD, CONVERT( VARCHAR, LOAD_TIME, 101 )AS LOAD_TIME, ASSM_QTY, [2/0], DA, WB, MOLD, PLAT, FORM, PACK, MIN_STAGE_NAME, MIN_STAGE_NAME_QTY, CYCLE_TIME, SHIPPABLE_MB, [ACTUAL CYCLE TIME],WO_STATUS FROM #CYCLE_TIME_RESULTS_TABLE CTRT
		WHERE ( (WO_STATUS = ''CLOSE'' ) OR (WO_STATUS = ''RUNNING'' AND MIN_STAGE_NAME is NOT null ) ) '
	END
	/*ELSE
	-- Else Loading Runnning and Closed Work Orders in specified Data Range(Flag is 2)
	BEGIN
		SET @SQLSTRING = N'SELECT PRODUCT_TYPE_ID, BASE_PRODUCT_NAME, PS_TYPE_NAME_ID, PS_TYPE_NAME, PROCESSSTEP_ID, ITEM_NUMBER, ITEM_DESC, LOT_ID, WORK_ORDER_NO, QTY_OUT, QTY_REJECTED, QTY_IN, CONVERT( VARCHAR, QAD_ORD_DATE, 101 ) AS QAD_ORD_DATE, CONVERT( VARCHAR, QAD_REL_DATE, 101 ) AS QAD_REL_DATE, CONVERT( VARCHAR, QAD_DUE_DATE, 101 ) AS QAD_DUE_DATE, CONVERT( VARCHAR, SOD, 101 ) AS SOD, QAD_SOD_LATE_DAYS, CONVERT( VARCHAR, REVISED_SOD, 101 ) AS REVISED_SOD, CONVERT( VARCHAR, LOAD_TIME, 101 )AS LOAD_TIME, ASSM_QTY, [2/0], DA, WB, MOLD, PLAT, FORM, PACK, MIN_STAGE_NAME, MIN_STAGE_NAME_QTY, CYCLE_TIME, SHIPPABLE_MB, [ACTUAL CYCLE TIME],WO_STATUS FROM #CYCLE_TIME_RESULTS_TABLE CTRT
		WHERE ( (WO_STATUS = ''CLOSE'' ) OR (WO_STATUS = ''RUNNING'' AND MIN_STAGE_NAME is NOT null ) ) '
	END*/
	
	--Search condition for Process Type
	IF @PS_TYPE_NAME <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND PS_TYPE_NAME = ''' +  @PS_TYPE_NAME + ''''
	END

	--Search condition for Base Product when @CARD_TYPE, @PACKAGE_TYPE, @MEMORY_SIZE not selected
	IF ( @BASE_PRODUCT_ID IS NOT NULL  AND @BASE_PRODUCT_ID <> 0 ) and (@CARD_TYPE = '0'and @PACKAGE_TYPE = '0' and @MEMORY_SIZE = '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in ( SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID) +')'
	END

	--Search condition for Part Number when @CARD_TYPE, @PACKAGE_TYPE, @MEMORY_SIZE not selected
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0 ) and (@CARD_TYPE = '0' and @PACKAGE_TYPE = '0' and @MEMORY_SIZE = '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id = '+convert(varchar,@PROCESSSTEP_ID)
	END

	--Search condition for @CARD_TYPE
	IF ( @CARD_TYPE IS NOT NULL AND ltrim(rtrim(@CARD_TYPE)) <> '' AND @CARD_TYPE <> '0' )
	BEGIN
		IF ( @BASE_PRODUCT_ID IS NOT NULL  AND @BASE_PRODUCT_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE P.P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID)+
			' AND (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''CARD TYPE'' and processstep_id = p.processstep_id ) = ''' +  @CARD_TYPE + ''''+')'
		END
		
		IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in (SELECT processstep_id FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''CARD TYPE'' AND PROCESSSTEP_ID ='+convert(varchar,@PROCESSSTEP_ID)+' and PARAMETER_VALUE = ''' +  @CARD_TYPE + ''''+')'
		END

		IF ( @BASE_PRODUCT_ID = 0 AND @PROCESSSTEP_ID = 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''CARD TYPE'' and processstep_id = CTRT.processstep_id ) = ''' +  @CARD_TYPE + ''''+')'
		END
	END

	--Search condition for @PACKAGE_TYPE
	IF ( @PACKAGE_TYPE IS NOT NULL AND ltrim(rtrim(@PACKAGE_TYPE)) <> '' AND @PACKAGE_TYPE <> '0' ) 
	BEGIN
		IF ( @BASE_PRODUCT_ID IS NOT NULL  AND @BASE_PRODUCT_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in (SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE P.P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID)+
			' AND (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' and processstep_id = p.processstep_id ) =  ''' +  @PACKAGE_TYPE + ''''+')'
		END

		IF ( @PROCESSSTEP_ID IS NOT NULL  AND @PROCESSSTEP_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in (SELECT processstep_id FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID''AND PROCESSSTEP_ID ='+convert(varchar,@PROCESSSTEP_ID)+' and PARAMETER_VALUE = ''' +  @PACKAGE_TYPE + ''''+')'
		END
		
		IF ( @BASE_PRODUCT_ID = 0 AND @PROCESSSTEP_ID = 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' and processstep_id = CTRT.processstep_id ) = ''' +  @PACKAGE_TYPE + ''''+')'
		END
	END

	--Search condition for @MEMORY_SIZE
	IF ( @MEMORY_SIZE IS NOT NULL AND ltrim(rtrim(@MEMORY_SIZE)) <> '' AND @MEMORY_SIZE > '0' )  
	BEGIN
		IF ( @BASE_PRODUCT_ID IS NOT NULL AND @BASE_PRODUCT_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in (SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE P.P_PARENT_PRODUCT_ID = '+convert(varchar,@BASE_PRODUCT_ID)+
			' AND (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''MEMORY SIZE'' and processstep_id = p.processstep_id ) =  ''' +  @MEMORY_SIZE + ''''+')'
		END

		IF ( @PROCESSSTEP_ID IS NOT NULL  AND @PROCESSSTEP_ID <> 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in (SELECT processstep_id FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''MEMORY SIZE'' AND PROCESSSTEP_ID ='+convert(varchar,@PROCESSSTEP_ID)+' and PARAMETER_VALUE   =  ''' +  @MEMORY_SIZE + ''''+')'
		END

		IF ( @BASE_PRODUCT_ID = 0 AND @PROCESSSTEP_ID = 0 )
		BEGIN
			SET @SQLSTRING = @SQLSTRING +' and CTRT.processstep_id in ( SELECT P.PROCESSSTEP_ID FROM PRODUCT P WHERE (SELECT distinct PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''MEMORY SIZE'' and processstep_id = CTRT.processstep_id )  =  ''' +  @MEMORY_SIZE + ''''+')'
		END
	END

	--For Dates
	IF @DATE_TYPE = 'byDueDate'
	BEGIN
		IF ( @FROM_DATE IS NOT NULL AND ltrim(rtrim(@FROM_DATE)) <> '' AND @FROM_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QAD_DUE_DATE >= ''' + convert(varchar, convert(datetime,@FROM_DATE) ) + ''''
		END
		IF ( @TO_DATE IS NOT NULL AND ltrim(rtrim(@TO_DATE)) <> '' AND @TO_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QAD_DUE_DATE  <= ''' + convert(varchar, convert(datetime,@TO_DATE) )  + ''''
		END
		IF( @FROM_DATE = '0' and @TO_DATE = '0' )
		BEGIN

			SET @SQLSTRING = @SQLSTRING + ' AND WO_STATUS = ''RUNNING'''
		END
	END
	ELSE --IF @DATE_TYPE = 'byRelDate'
	BEGIN
		IF ( @FROM_DATE IS NOT NULL AND ltrim(rtrim(@FROM_DATE)) <> '' AND @FROM_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QAD_REL_DATE >= ''' + convert(varchar, convert(datetime,@FROM_DATE) ) + ''''
		END

		IF ( @TO_DATE IS NOT NULL AND ltrim(rtrim(@TO_DATE)) <> '' AND @TO_DATE <> '0' ) 
		BEGIN
			set @SQLSTRING = @SQLSTRING + ' and QAD_REL_DATE <= ''' + convert(varchar, convert(datetime,@TO_DATE) )  + ''''
		END
		IF( @FROM_DATE = '0' and @TO_DATE = '0' )
		BEGIN

			SET @SQLSTRING = @SQLSTRING + ' AND WO_STATUS = ''RUNNING'''
		END		
	END
	SET @SQLSTRING = @SQLSTRING + ' ORDER BY QAD_DUE_DATE '
	--PRINT @SQLSTRING
	EXEC (@SQLSTRING)

	DELETE FROM #CYCLE_TIME_RESULTS_TABLE
END

